package com.samson.empapp.dao; 

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import com.samson.empapp.dbo.DepartmentsDbo;
import com.samson.empapp.util.DbUtil;

public class DepartmentsDao implements IDepartmentsDao {
	
	static Logger logger = Logger.getLogger(DepartmentsDao.class);

	@Override
	public DepartmentsDbo findById(int departmentId) {
		DepartmentsDbo dbo = null; 
		Connection con = DbUtil.getOracleXEConn(); 
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		StringBuilder sb = null;
		try {
			sb = new StringBuilder();
			sb.append("SELECT "); 
			sb.append("DEPARTMENT_ID departmentId, "); 
			sb.append("DEPARTMENT_NAME departmentName, "); 
			sb.append("MANAGER_ID managerId, "); 
			sb.append("LOCATION_ID locationId "); 
			sb.append("FROM DEPARTMENTS "); 
			sb.append("where DEPARTMENT_ID ="+ departmentId);
			pstmt = con.prepareStatement(sb.toString());
			
			logger.info("Executing Query " + sb.toString());
			rs = pstmt.executeQuery();
			if (rs.next()) {
				dbo = new DepartmentsDbo();
				dbo.setDepartmentId(departmentId);
				dbo.setDepartmentName(rs.getString("departmentName"));
				dbo.setManagerId(rs.getInt("managerId"));
				dbo.setLocationId(rs.getInt("locationId"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			logger.error("Exception" + e);
		} finally {
			DbUtil.closeDBConnection(pstmt, rs, con, sb);
		}
		return dbo;
	}

	@Override
	public List<DepartmentsDbo> findAll() {
		List<DepartmentsDbo> returnList = new ArrayList<DepartmentsDbo>();
		DepartmentsDbo obj = null;
		Connection con = DbUtil.getOracleXEConn();
		PreparedStatement pstmt = null;
		ResultSet rs = null; 
		StringBuilder sb = null;
		try {
			sb = new StringBuilder();
			sb.append("SELECT "); 
			sb.append("DEPARTMENT_ID departmentId, "); 
			sb.append("DEPARTMENT_NAME departmentName, "); 
			sb.append("MANAGER_ID managerId, "); 
			sb.append("LOCATION_ID locationId "); 
			sb.append("FROM DEPARTMENTS "); 
			pstmt = con.prepareStatement(sb.toString());
			logger.info("Executing Query " + sb.toString());
			rs = pstmt.executeQuery(sb.toString());
			while (rs.next()) {
				obj = new DepartmentsDbo();
				obj.setDepartmentId(rs.getInt("departmentId"));
				obj.setDepartmentName(rs.getString("departmentName"));
				obj.setManagerId(rs.getInt("managerId"));
				obj.setLocationId(rs.getInt("locationId"));
				
				returnList.add(obj);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			logger.error("Exception" + e);
		} finally {
			DbUtil.closeDBConnection(pstmt, rs, con, sb);
		}
		return returnList;
	}

	@Override
	public void insert(DepartmentsDbo dbo) throws Exception {
		Connection con = DbUtil.getOracleXEConn();
		PreparedStatement pstmt = null;
		StringBuilder sb = null;
		try {
			sb = new StringBuilder();
			sb.append("INSERT INTO \"HR\".\"DEPARTMENTS\" "); 
			sb.append("(DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID) VALUES "); 
			sb.append("(?, ?, ?, ?) ");

			pstmt = con.prepareStatement(sb.toString());
			logger.info("Executing Query " + sb.toString());
			pstmt.setInt(1, dbo.getDepartmentId());
			pstmt.setString(2, dbo.getDepartmentName());
			pstmt.setInt(3, dbo.getManagerId());
			pstmt.setInt(4, dbo.getLocationId());
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			logger.error("Exception" + e);
			throw e;
		} finally {
			DbUtil.closeDBConnection(pstmt, con, sb);
		}
}

}
